package model.dao;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.SQLAdapter;
import model.bean.Tarefa;

public class TarefaDAO{
	private static final String SQL_SELECT_QUANTIDADE_POR_USUARIO = "SELECT t.idsessao, COUNT(t.idsessao) AS count FROM tarefa t INNER JOIN app a WHERE a.idsessao = t.idsessao AND a.nome = ? AND t.estado = 'finish' AND t.periodo >= ? AND t.periodo <= ? GROUP BY t.idsessao ORDER BY t.idsessao ASC";
	private static final String SQL_SELECT_TEMPO_MEDIO = "SELECT t.nome, TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(a.duracao))/COUNT(a.duracao)), '%H:%i:%s') AS media FROM tarefa t INNER JOIN app a WHERE t.idsessao = a.idsessao AND a.nome = ? AND t.estado = 'finish' AND t.duracao > 0 AND t.periodo >= ? AND t.periodo <= ? GROUP BY t.nome ORDER BY t.nome ASC";
	private static SQLAdapter sqlAdapter;
		
    private static SQLAdapter getSqlAdapter(){    	
    	if(sqlAdapter == null){
    		sqlAdapter = new SQLAdapter(new File("bd/bd_config.xml"));
    	}
		return sqlAdapter;
	}
    
	public static void setSqlAdapter(String driver, String protocol, String host, String database, String username, String password){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(driver, protocol, host, database, username, password);			
		}else{
			getSqlAdapter().getDataSource().changeConnection(driver, protocol, host, database, username, password);
		}
	}
	
	public static void setSqlAdapter(File configFile){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(configFile);
		}else{
			getSqlAdapter().getDataSource().changeConnection(configFile);
		}
	}
	
	public static List<Tarefa> queryQuantidadePorUsuario(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
    	int cont = 0;
		if(getSqlAdapter() != null){
    		List<Tarefa> tarefaList = new ArrayList<Tarefa>();
        	
            PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_QUANTIDADE_POR_USUARIO);
            if(ps != null){
               	ps.setString(1, aplicacaoNome);
               	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
               	ResultSet resultSet = ps.executeQuery();
               	while(resultSet.next()){
               		cont++;
               		tarefaList.add(new Tarefa("Usuário "+cont, null, resultSet.getString("count"), null, null));
               	}
               	getSqlAdapter().close();
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
            
            return tarefaList;
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
    
	public static List<Tarefa> queryTempoMedio(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
		if(getSqlAdapter() != null){
    		List<Tarefa> tarefaList = new ArrayList<Tarefa>();
        	
            PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_TEMPO_MEDIO);
            if(ps != null){            	
               	ps.setString(1, aplicacaoNome);
               	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
               	ResultSet resultSet = ps.executeQuery();
               	while(resultSet.next()){
               		tarefaList.add(new Tarefa(null, null, resultSet.getString("t.nome"), null, resultSet.getTime("media")));
               	}
               	getSqlAdapter().close();
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
            
            return tarefaList;
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
	
    public static void main(String[] args){
    	try{
			List<Tarefa> tarefaList = queryQuantidadePorUsuario("Tvilo Viewer", "2012-8-7", "2012-11-1");
			for(int i=0; i<tarefaList.size(); i++){
				System.out.println(tarefaList.get(i));
			}
			
			System.out.println();
			
			List<Tarefa> tarefaList2 = queryTempoMedio("Tvilo Viewer", "2012-8-7", "2012-11-1");
			for(int i=0; i<tarefaList2.size(); i++){
				System.out.println(tarefaList2.get(i));
			}
			
			System.out.println("DONE");
			
		}catch(NullPointerException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}
